package com.css.fxfzcpxx.modules.informationDelivery.repository.impl;

import com.css.fxfzcpxx.constants.YNEnum;
import com.css.fxfzcpxx.modules.categoryView.param.QueParam;
import com.css.fxfzcpxx.modules.categoryView.vo.CategoryViewVo;
import com.css.fxfzcpxx.modules.informationAchievement.entity.InfAchievement;
import com.css.fxfzcpxx.modules.informationDelivery.entity.InfDelivery;
import com.css.fxfzcpxx.modules.informationDelivery.param.ClaCasParams;
import com.css.fxfzcpxx.modules.informationDelivery.param.QueParams;
import com.css.fxfzcpxx.modules.informationDelivery.repository.InfDeliveryRepository;
import com.css.fxfzcpxx.modules.informationDelivery.vo.InfDeliveryVo;
import com.css.fxfzcpxx.util.PlatformObjectUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class InfDeliveryRepositoryImpl implements InfDeliveryRepository {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    private static final String TABLE_NAME = "cpxx_information_delivery";

    @Override
    public Map<String, Object> getPage(QueParams queParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select b.type_name meansType,a.* from "+TABLE_NAME+" a left join cpxx_type_manage b on a.means_type_id=b.id");
        StringBuilder whereSql = new StringBuilder(" where a.del_flag = '"+ YNEnum.N.toString()+"' and a.del_flag = '"+ YNEnum.N.toString()+"'");
        StringBuilder orderSql = new StringBuilder(" order by a.create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        String limitSql = String.format(" limit %d  offset %d ", pageSize, firstIndex);
        //StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        if (!PlatformObjectUtils.isEmpty(queParams.getReleaseStatus())) {
            whereSql.append(" and a.release_status = '"+queParams.getReleaseStatus()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getMeansName())) {
            whereSql.append(" and a.means_name like '%" + queParams.getMeansName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getReleaseColumn())) {
            whereSql.append(" and a.release_column = '" + queParams.getReleaseColumn() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getMeansTypeId())) {
            whereSql.append(" and a.means_type_id = '" + queParams.getMeansTypeId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getMeansType())) {
            whereSql.append(" and a.means_type = '" + queParams.getMeansType() + "'");
        }
        /*if (!PlatformObjectUtils.isEmpty(queParams.getStartTime())&!PlatformObjectUtils.isEmpty(queParams.getEndTime())) {
            whereSql.append("and achievement_date between '"+queParams.getStartTime()+"' and '"+queParams.getEndTime()+"'");
        }*/

        //时间范围
        if (!PlatformObjectUtils.isEmpty(queParams.getStartTime()) & !PlatformObjectUtils.isEmpty(queParams.getEndTime())) {
            whereSql.append("and a.release_date between '" + queParams.getStartTime() + "' and '" + queParams.getEndTime() + "'");
        }
        //大于开始时间
        if (!PlatformObjectUtils.isEmpty(queParams.getStartTime()) & PlatformObjectUtils.isEmpty(queParams.getEndTime())) {
            whereSql.append("and a.release_date  >='" + queParams.getStartTime() + "'");
        }
        //小于结束时间
        if ( !PlatformObjectUtils.isEmpty(queParams.getEndTime()) & PlatformObjectUtils.isEmpty(queParams.getStartTime())) {
            whereSql.append("and a.release_date  <='" + queParams.getEndTime() + "'");
        }


        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(InfDelivery.class)).size();
        List<InfDelivery> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(InfDelivery.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public void save(InfDelivery infDelivery) {
        String sql = "insert into "+TABLE_NAME+"(id,means_name,release_status,release_date,release_column,means_upload,means_describe," +
                "remark,create_user,create_time,update_user,update_time,del_flag,is_permit_download,means_type_id) " +
                "values(:id, :meansName, :releaseStatus, :releaseDate, :releaseColumn, :meansUpload, :meansDescribe, " +
                ":remark, :createUser, :createTime, :updateUser, :updateTime, :delFlag, :isPermitDownload, :meansTypeId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(infDelivery);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void recallOrPublish(String releaseStatus, List<String> ids) {
        String sql = "update "+TABLE_NAME+" set release_status = '"+releaseStatus+"' where id in  ('" + String.join("','", ids) + "')";
        jdbcTemplate.update(sql);
    }

    @Override
    public void delete(List<String> ids) {
        String sql = "update "+TABLE_NAME+" set del_flag = '"+YNEnum.Y.toString()+"' where id in  ('" + String.join("','", ids) + "')";
        jdbcTemplate.update(sql);
    }

    @Override
    public InfDelivery findById(String id) {
        String sql = "select * from "+TABLE_NAME+" where id = ? ";
        InfDelivery infDelivery = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(InfDelivery.class), id);
        return infDelivery;
    }

    @Override
    public void publishedDataupdate(InfDelivery infDelivery) {
        String sql = "update "+TABLE_NAME+" set means_describe = :meansDescribe ," +
                "update_user = :updateUser ,update_time = :updateTime where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(infDelivery);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void unpublishedDataupdate(InfDelivery infDelivery) {
        String sql = "update "+TABLE_NAME+" set means_name= :meansName ,release_column= :releaseColumn," +
                "means_describe = :meansDescribe ,means_upload= :meansUpload ,is_permit_download = :isPermitDownload ," +
                "remark = :remark ,update_user = :updateUser ,update_time = :updateTime ,means_type_id= :meansTypeId where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(infDelivery);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public List<InfDelivery> getDataById(List<String> ids) {
        String sql = "select b.type_name meansType,a.* from cpxx_service_case a left join cpxx_type_manage b on a.means_type_id=b.id where a.id in  ('" + String.join("','", ids) + "')";
        List<InfDelivery> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(InfDelivery.class));
        return list;
    }

    @Override
    public List<InfDelivery> getMoveData(QueParams queParams) {
        StringBuilder sql = new StringBuilder("select * from "+TABLE_NAME+"");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '"+ YNEnum.N.toString()+"'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        if (!PlatformObjectUtils.isEmpty(queParams.getMeansName())) {
            whereSql.append(" and means_name like '%" + queParams.getMeansName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getReleaseColumn())) {
            whereSql.append(" and release_column = '" + queParams.getReleaseColumn() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getMeansType())) {

            List<String> ids = Arrays.asList(queParams.getMeansTypeId().split(","));
            whereSql.append(" and means_type_id in  ('" + String.join("','", ids) + "')");
        }

        sql.append(whereSql).append(orderSql);
        List<InfDelivery> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(InfDelivery.class));
        return list;
    }

    @Override
    public void meansTypeUpdate(String id,String meansTypeId) {
        String sql = "update "+TABLE_NAME+" set means_type_id = ? where id = ?";
        jdbcTemplate.update(sql,meansTypeId,id);
    }

    @Override
    public Map<String, Object> getClaCasPage(ClaCasParams claCasParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select b.type_name meansType,a.* from "+TABLE_NAME+" a left join cpxx_type_manage b on a.means_type_id=b.id");
        StringBuilder whereSql = new StringBuilder(" where a.del_flag = '"+ YNEnum.N.toString()+"' and b.del_flag = '"+ YNEnum.N.toString()+"' and a.release_status='"+YNEnum.N.toString()+"' ");
        StringBuilder orderSql = new StringBuilder(" order by a.create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        String limitSql = String.format(" limit %d  offset %d ", pageSize, firstIndex);
//        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        if (!PlatformObjectUtils.isEmpty(claCasParams.getReleaseColumn())) {
            whereSql.append(" and a.release_column = '" + claCasParams.getReleaseColumn() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(claCasParams.getReleaseDate())) {
            whereSql.append(" and a.create_time >=(select now() - interval '"+claCasParams.getReleaseDate()+"') and a.create_time <=now()");
        }
        if (!PlatformObjectUtils.isEmpty(claCasParams.getMeansTypeId())) {
            whereSql.append(" and a.means_type_id = '" + claCasParams.getMeansTypeId() + "'");
        }

        if (!PlatformObjectUtils.isEmpty(claCasParams.getMeansName())) {
            whereSql.append(" and a.means_name like '%" + claCasParams.getMeansName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(claCasParams.getReleaseStatus())) {
            whereSql.append(" and a.release_status = '"+claCasParams.getReleaseStatus()+"'");
        }


        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(InfDelivery.class)).size();
        List<InfDeliveryVo> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(InfDeliveryVo.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public List<CategoryViewVo> getData(QueParam queParam) {
        StringBuilder sql = new StringBuilder("select means_name,release_column module2," +
                "release_date publishingDate,create_time,means_upload attachId  from "+TABLE_NAME+"");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '"+ YNEnum.N.toString()+"' and release_status='0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        if (!PlatformObjectUtils.isEmpty(queParam.getModule2())) {
            whereSql.append(" and release_column = '"+queParam.getModule2()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(queParam.getKeyword())) {
            whereSql.append(" and means_name like '%" + queParam.getKeyword() + "%'");
        }

        //时间范围
        if (!PlatformObjectUtils.isEmpty(queParam.getStartTime()) & !PlatformObjectUtils.isEmpty(queParam.getEndTime())) {
            whereSql.append("and release_date between '" + queParam.getStartTime() + "' and '" + queParam.getEndTime() + "'");
        }
        //大于开始时间
        if (!PlatformObjectUtils.isEmpty(queParam.getStartTime()) & PlatformObjectUtils.isEmpty(queParam.getEndTime())) {
            whereSql.append("and release_date  >='" + queParam.getStartTime() + "'");
        }
        //小于结束时间
        if ( !PlatformObjectUtils.isEmpty(queParam.getEndTime()) & PlatformObjectUtils.isEmpty(queParam.getStartTime())) {
            whereSql.append("and release_date  <='" + queParam.getEndTime() + "'");
        }
        List<CategoryViewVo> list = jdbcTemplate.query(sql.append(whereSql).append(orderSql).toString(), new BeanPropertyRowMapper<>(CategoryViewVo.class));
        return list;
    }

    @Override
    public Integer getDownload(String unit) {
        String sql = "select sum(b.download_count) from "+TABLE_NAME+" a  " +
                "inner join  sys_attachment_record b on a.means_upload=b.attachment_id " +
                "where a.del_flag='0' and EXTRACT("+unit+" FROM b.download_time) = EXTRACT("+unit+" FROM CURRENT_DATE) ";
        Integer total = jdbcTemplate.queryForObject(sql, Integer.class);
        if(PlatformObjectUtils.isEmpty(total)){
            return 0;
        }
        return total;
    }

    @Override
    public List<InfDeliveryVo> getLatestInformation() {
        String sql = "select * from "+TABLE_NAME+" where del_flag='0' and release_status='0' order by create_time desc limit 6";
        List<InfDeliveryVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(InfDeliveryVo.class));
        return list;
    }
}
